﻿using Microsoft.Xrm.Sdk.Metadata;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
using Project.Co_DataTransformationPrototype.Data;

namespace Project.Co_DataTransformationPrototype.temp
{
    internal static class StagingDatabase
    {

        #region Metadata Tables
        internal static bool checkForMetadataTables()
        {
            bool blnFound = false;
            try
            {
                DataTable dtResult = new DataTable();
                string[] MetadataTables;

                MetadataTables = temp.Constants.Database.Strings.MetadataTableNames.Split(',');
                string strSQL = StagingScripts.GetScriptCommand("SELECT_METADATA_TABLE_NAMES", MetadataTables);

                dtResult = Data.SQLUtilities.executeSQL(Data.DatabaseSettings.GetInstance().ConnectionString, strSQL);

                if (dtResult != null && dtResult.Rows.Count == 2)
                    blnFound = true;
            }
            catch (SqlException)
            {
                throw;
            }

            return blnFound;
        }

        internal static void createMetadataTables(string resourceFilePath)
        {
            SQLUtilities.executeSqlFile(resourceFilePath);
        }
        #endregion

        #region Database Schema
        internal static bool selectDatabaseSchema()
        {
            try
            {
                DataTable TempData = new DataTable();
                string[] Param = new string[1];

                Param[0] = DatabaseSettings.STAGING_SCHEMA_NAME;

                string strSQL = StagingScripts.GetScriptCommand("SELECT_SCHEMA_DETAILS", Param);

                TempData = SQLUtilities.executeSQL(DatabaseSettings.GetInstance().ConnectionString, strSQL);

                if (TempData != null && TempData.Rows.Count > 0)
                    return true;
                else
                    return false;
            }
            catch (SqlException)
            {
                throw;
            }
        }

        internal static void createDatabaseSchema()
        {
            try
            {
                string[] Param = new string[1];
                Param[0] = DatabaseSettings.STAGING_SCHEMA_NAME;

                string strSQL = StagingScripts.GetScriptCommand("CREATE_SCHEMA", Param);
                SQLUtilities.executeNonQuerySQL(DatabaseSettings.GetInstance().ConnectionString, strSQL);
            }
            catch (SqlException)
            {
                throw;
            }
        }
        #endregion

        #region Insert Values
        internal static void insertEntityValues(string entityName, string displayName, bool isReference, string description, bool isIntersect, int isActivity)
        {

            try
            {
                string[] Param = new string[13];
                string strSqlText = string.Empty;

                if (description.IndexOf("'", StringComparison.OrdinalIgnoreCase) > -1)
                    description = description.Replace("'", "''");

                Param[0] = entityName;
                Param[1] = isReference == true ? "1" : "0";
                Param[2] = isIntersect == true ? "1" : "0";
                Param[3] = isActivity.ToString(CultureInfo.InvariantCulture);
                Param[4] = displayName;
                Param[5] = "null";
                Param[6] = "null";

                Param[7] = "null";
                Param[8] = "null";
                Param[9] = description;
                Param[10] = "0";
                Param[11] = "";
                Param[12] = "1";

                strSqlText = StagingScripts.GetScriptCommand("INSERT_AVA_ENTITY_ROW", Param);
                SQLUtilities.executeNonQuerySQL(DatabaseSettings.GetInstance().ConnectionString, strSqlText);
            }
            catch
            {
                throw;
            }
        }

        internal static void insertAttributeValues(string entityName, string attributeName, string displayName, string crmDataType, string sqlDataType, string dataLength, bool validToCreate, bool validToUpdate, string operationType, bool isFK, string fkSourceType, string relatedEntity, string relatedAttribute, string fkSourceType2, string relatedEntity2, string relatedAttribute2, bool isRequire, int lookType)
        {
            try
            {
                string[] Param;
                string strSqlText = string.Empty;

                if (displayName.IndexOf("'", StringComparison.OrdinalIgnoreCase) > -1)
                    displayName = displayName.Replace("'", "''");

                Param = new string[18];
                Param[0] = entityName;
                Param[1] = attributeName;
                Param[2] = displayName;
                Param[3] = crmDataType;
                Param[4] = sqlDataType;
                Param[5] = dataLength;
                Param[6] = validToCreate == true ? "1" : "0";
                Param[7] = validToUpdate == true ? "1" : "0";
                Param[8] = operationType;
                Param[9] = isFK == true ? "1" : "0";
                Param[10] = fkSourceType;
                Param[11] = relatedEntity;
                Param[12] = relatedAttribute;
                Param[13] = fkSourceType2;
                Param[14] = relatedEntity2;
                Param[15] = relatedAttribute2;
                Param[16] = isRequire == true ? "1" : "0";
                Param[17] = lookType.ToString(CultureInfo.InvariantCulture);

                strSqlText = StagingScripts.GetScriptCommand("INSERT_ATTRIBUTE_DETAILS", Param);
                SQLUtilities.executeNonQuerySQL(DatabaseSettings.GetInstance().ConnectionString, strSqlText);
            }
            catch
            {
                throw;
            }
        }
        #endregion

        internal static void createStagingDatabase(IList<Entity> entityList, Dictionary<int, EntityMetadata> crmEntityData = null)
        {
            // Generate CREATE statement for each modified entity
            foreach (Entity entity in entityList)
            {
                if (entity.IsImported && crmEntityData == null)
                {
                    Microsoft.VisualBasic.Interaction.MsgBox("Entity claims to be imported, but there is no recorded import data", Microsoft.VisualBasic.MsgBoxStyle.Exclamation, "Data Publishing - Import Check Error");
                    continue; 
                    // TODO: log failed entities
                }

                if (!entity.IsImported || (entity.IsImported && crmEntityData.ContainsKey(entity.Name.GetHashCode())))
                {
                    createTable(entity);
                    if (entity.IsImported)
                    {
                        EntityMetadata crmEntity = crmEntityData[entity.Name.GetHashCode()];
                        insertEntityValues(crmEntity.LogicalName, crmEntity.DisplayName.ToString(), false, crmEntity.Description.ToString(), crmEntity.IsIntersect.Value, Convert.ToInt32(crmEntity.IsActivity, CultureInfo.InvariantCulture));
                        foreach (AttributeMetadata attribute in crmEntity.Attributes)
                            insertAttributeValues(attribute.EntityLogicalName, attribute.LogicalName, attribute.DisplayName.ToString(), attribute.AttributeType.Value.ToString(), "NVARCHAR", "MAX", attribute.IsValidForCreate.Value, attribute.IsValidForUpdate.Value,
                                null, false, null, null, null, null, null, null, attribute.IsPrimaryId.Value, 0);
                    }
                    else
                    {
                        insertEntityValues(entity.Name, entity.Name, false, "", false, 0);
                        foreach (EntityAttribute attribute in entity.EntityAttributes)
                            insertAttributeValues(attribute.Entity.Name, attribute.Name, "", "", "NVARCHAR", "-1", false, false, null, false, null, null, null, null, null, null, false, 0); 
                    }
                }
            }
        }

        private static void createTable(Entity entity)
        {
            StringBuilder strbCreateTable = new StringBuilder();

            // DROP table command                    
            strbCreateTable.AppendLine(string.Format(CultureInfo.InvariantCulture, Constants.Database.Queries.DropIfExists, entity.Name, entity.Name) + Environment.NewLine);
            // CREATE table command
            strbCreateTable.AppendLine(string.Format(CultureInfo.InvariantCulture, Constants.Database.Queries.CreateTable, entity.Name));
            strbCreateTable.Append(Constants.Strings.OpenBracket);

            // Add the table rows (which are the entity's attributes)
            if (entity.EntityAttributes.Count > 0)
            {
                foreach (EntityAttribute attribute in entity.EntityAttributes)
                {
                    string AttributeName = attribute.Name;
                    strbCreateTable.Append(Constants.Strings.OpenSquareBracket + AttributeName + Constants.Strings.CloseSquareBracket);

                    // TODO: Add actual type (as in attribute.Type.value + " (" + attribute.TypeDataLength.value + ")" )
                    strbCreateTable.Append(Constants.Database.Queries.NvarChar255);

                    // TODO: If the column type is of type Lookup, change sql-statement
                    strbCreateTable.Append(Constants.Strings.Comma);
                }
                strbCreateTable.Replace(",", "", strbCreateTable.Length - 2, 1);

                // TODO: Add possible Indexes
            }
            else
            {
                // Table must contain atleast 1 row
                strbCreateTable.Append(Constants.Strings.OpenSquareBracket + "Id" + Constants.Strings.CloseSquareBracket);
                strbCreateTable.Append(Constants.Database.Queries.NvarChar255);
            }
            strbCreateTable.Append(Constants.Strings.CloseBracket + " \n END \n");

            // Create entity table by executing SQL command
            try
            {
                SQLUtilities.executeNonQuerySQL(DatabaseSettings.GetInstance().ConnectionString, strbCreateTable.ToString());
                // TODO: Create Activity related extra table
                Microsoft.VisualBasic.Interaction.MsgBox("Table created for " + entity.Name, Microsoft.VisualBasic.MsgBoxStyle.OkOnly, "Data Publishing - Staging Database");

            }
            catch (SqlException exp)
            {
                // if entity table creation failes log error and display it to user and continue to next entity
                Microsoft.VisualBasic.Interaction.MsgBox("Failed to create table for " + entity.Name + Environment.NewLine + exp.Message, Microsoft.VisualBasic.MsgBoxStyle.OkOnly, "Data Publishing - Error Staging Database");
            }
        }

    }
}
